package com.yellowpage.white.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class QueryAssistantSQL {
	protected final Log log = LogFactory.getLog(getClass());

	private String sql = null;
	private Map parems = null;

	// SQL条件的列表
	private List conditionList = null;
	private List paremsList = null;
	private List dataTypeList = null;
	private String tail;

	// 数据类型常量
	public static String DATA_TYPE_STRING = "STRING";
	public static String DATA_TYPE_DATE = "DATE";

	/**
	 * 构造器，提供静态的SQL语句
	 * 
	 * @param sql
	 */
	public QueryAssistantSQL(String sql) {
		this.sql = sql;

		this.conditionList = new ArrayList();
		this.paremsList = new ArrayList();
		this.dataTypeList = new ArrayList();
	}

	/**
	 * 添加SQL条件，默认数据类型为String
	 * 
	 * @param condition
	 * @return
	 */
	public QueryAssistantSQL addCondition(String condition) {
		this.addCondition(condition, this.DATA_TYPE_STRING);
		return this;
	}

	/**
	 * 添加SQL条件
	 * 
	 * @param condition
	 * @param type
	 * @return
	 */
	public QueryAssistantSQL addCondition(String condition, String type) {
		this.conditionList.add(condition);
		this.paremsList.add(getParemName(condition));
		this.dataTypeList.add(type);
		return this;
	}

	/**
	 * 执行查询的方法，Map封装了传入的参数
	 * 
	 * @param conn
	 * @param parems
	 * @return
	 */
	public List query(Connection conn, Map parems) {

		List valueList = parseSQL(parems);

		return excuteQuery(conn, this.sql, valueList);
	}

	public List queryByPage(Connection conn, Map parems, PageRender pageRender,
			boolean isNeedCount) {
		List valueList = parseSQL(parems);

		
		if(isNeedCount){
			// 获得记录总计
			String countSQL = " select count(*) as RECORDSUM_ from ( " + this.sql
					+ " ) as theQuerySQL  ";
			List countResult = excuteQuery(conn, countSQL, valueList);
	
			BigDecimal totle = (BigDecimal) ((Map) countResult.get(0)).get("RECORDSUM_");
			pageRender.setTotals(totle.intValue());
		}else{
			pageRender.setTotals(-1);
			
		}

		// System.out.println(">> QueryAssistantSQL totle:" + totle);

		// 获得分页数据
		int from = (pageRender.getPage() - 1) * pageRender.getPerPageNum();
		int to = pageRender.getPage() * pageRender.getPerPageNum();

		// System.out.println(">> QueryAssistantSQL from:" + from);
		// System.out.println(">> QueryAssistantSQL   to:" + to);

		String pageSQL = " select * from ( select row_.*, rownum rownum_ from ( "
				+ this.sql + "	)  row_ where rownum <= ?) where rownum_ > ?";

		valueList.add(String.valueOf(to));
		valueList.add(String.valueOf(from));

		return excuteQuery(conn, pageSQL, valueList);
	}

	public List queryByPage(Connection conn, Map parems, PageRender pageRender) {

		List valueList = parseSQL(parems);

		// 获得记录总计
		String countSQL = " select count(*) as RECORDSUM_ from ( " + this.sql
				+ " ) as theQuerySQL ";
		List countResult = excuteQuery(conn, countSQL, valueList);

		BigDecimal totle = (BigDecimal) ((Map) countResult.get(0))
				.get("RECORDSUM_");
		pageRender.setTotals(totle.intValue());

		// System.out.println(">> QueryAssistantSQL totle:" + totle);

		// 获得分页数据
		int from = (pageRender.getPage() - 1) * pageRender.getPerPageNum();
		int to = pageRender.getPage() * pageRender.getPerPageNum();

		// System.out.println(">> QueryAssistantSQL from:" + from);
		// System.out.println(">> QueryAssistantSQL   to:" + to);

		String pageSQL = " select * from ( select row_.*, rownum rownum_ from ( "
				+ this.sql + "	)  row_ where rownum <= ?) where rownum_ > ?";

		valueList.add(String.valueOf(to));
		valueList.add(String.valueOf(from));

		return excuteQuery(conn, pageSQL, valueList);
	}

	private List parseSQL(Map parems) {
		/*
		 * 依据传入的参数，组装SQL语句，处理SQL条件
		 */
		List valueList = new ArrayList();
		for (int i = 0; i < paremsList.size(); i++) {
			String parem = (String) paremsList.get(i);
			String type = (String) dataTypeList.get(i);
			String value;
			if (parems.get(parem) != null) {
				if (type.equals(this.DATA_TYPE_DATE)) {
					Date date = (Date) parems.get(parem);
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					value = sdf.format(date);
				} else {
					value = (String) parems.get(parem);
				}
				if (value != null && value.length() > 0) {
					String condition = (String) conditionList.get(i);
					this.sql += " " + condition.replaceAll(":" + parem, "?");
					valueList.add(value);
				}
			}

		}
		if (this.tail != null && !this.tail.equals(""))
			this.sql += " " + this.tail;

		return valueList;
	}

	private List excuteQuery(Connection conn, String excuteSQL, List valueList) {
		log.warn(">> QueryAssistantSQL excuteSQL:" + excuteSQL);
		log.warn(">> QueryAssistantSQL valueList:" + valueList);
		
		
		try {
			PreparedStatement ps = conn.prepareStatement(excuteSQL);
			for (int i = 0; i < valueList.size(); i++) {
				String tempValue = (String) valueList.get(i);
				ps.setString(i + 1, tempValue);
				
				log.warn(">> QueryAssistantSQL setString:" + tempValue);
			}

			ResultSet rs = ps.executeQuery();

			// 处理结果集
			List resultList = processResultSet(rs);

			ps.close();
			rs.close();
			return resultList;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;

	}

	/**
	 * 解析字符串中的":parem"字段，提取"parem",现只提取一个
	 * 
	 * @param condition
	 * @return
	 */
	private String getParemName(String condition) {
		String lastStr = condition.substring(condition.indexOf(":") + 1);
		return lastStr.substring(0, lastStr.indexOf(" "));
	}

	/**
	 * 处理ResultSet，把数据封装成List(Map<column, value>)
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private List processResultSet(ResultSet rs) throws SQLException {

		List resultList = new ArrayList();

		int columnCount = rs.getMetaData().getColumnCount();
		/**
		 * 把ResultSet的数据放入ResultList
		 */
		while (rs.next()) {
			Map resultMap = new HashMap();
			for (int i = 0; i < columnCount; i++) {
				String columnName = rs.getMetaData().getColumnName(i + 1);
				resultMap.put(columnName, rs.getObject(columnName));

			}
			resultList.add(resultMap);
		}

		return resultList;
	}

	public String getTail() {
		return tail;
	}

	public void setTail(String tail) {
		this.tail = tail;
	}

	/*
	 * 这些修改的sql月经
	 */
	public int updateSql(Connection conn) throws SQLException {
		PreparedStatement pstmt = null;
		int rs = 0;
		try {

			pstmt = conn.prepareStatement(this.sql);

			rs = pstmt.executeUpdate();

			return rs;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null)
				pstmt.close();
			return 0;
		}
	}
}
